package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import util.DBTool;
import util.Encoding;

import model.Group;
import model.HeadImg;
import model.Post;
import model.Star;
import model.StarHistory;
import model.User;
import DAO.StarDAO;

public class StarImple implements StarDAO {

	public List<User> getMemberList(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		List<User> users = new ArrayList<User>();
		User user = null;
		String sql = "SELECT u.*,g.groupName as groupName,h.ImgSrc as ImgSrc FROM " 
			+ "T_User as u,T_Group as g,T_HeadImg as h" 
			+ " where u.groupId = ? and u.groupID = g.id and u.headImg = h.id and u.state = 0";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();

			while(rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setJoinTime(rs.getDate("joinTime"));
				user.setSex(rs.getString("sex"));
				//user.setGroup(new Group(rs.getInt("groupID"),rs.getString("groupName")));
			user.setGroup(new Group(rs.getInt("groupID"),Encoding.isoToGBK(rs.getString("groupName"))));
				user.setPost(rs.getInt("post"));
				user.setPhone(rs.getString("phone"));
				user.setMajor(Encoding.isoToGBK(rs.getString("major")));
			//user.setMajor(rs.getString("major"));
				user.setHeadImg(new HeadImg());
				user.getHeadImg().setImgSrc(rs.getString("ImgSrc"));
				user.setQq(rs.getString("qq"));
				user.setLogintime(rs.getInt("loginTime"));
				user.setJiFen(rs.getInt("jifen"));
				
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return users;
	}

	public int addStar(Star star) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Stars set starId = ?, evaluateDate = ? , description = ?, bookName = ?, state = 1" + 
						" where qishu = ? and groupID = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, star.getUser().getId());
			pstmt.setString(2, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setString(3, star.getDescription());
			pstmt.setString(4, star.getBookName());
			pstmt.setString(5, star.getQishu());
			pstmt.setInt(6, star.getGroupId());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int openStar(String qishu) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select * from T_Stars where qishu = ?" ;

		try {
			System.out.println(qishu);
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, qishu);
			rs = pstmt.executeQuery();
			if(!(rs.next())) {
				sql = "insert into T_Stars(qishu, groupId) values(?, ?)" ;
				pstmt = dbt.getPstmt(sql);
				pstmt.setString(1, qishu);
				pstmt.setInt(2, 2);
				
				int urs = pstmt.executeUpdate();
				
				sql = "insert into T_Stars(qishu, groupId) values(?, ?)" ;
				pstmt = dbt.getPstmt(sql);
				pstmt.setString(1, qishu);
				pstmt.setInt(2, 3);
				urs = pstmt.executeUpdate();
				if(urs != 0) {
					result = 1;
				}
			}else {
				result = 2;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		System.out.println(result);
		return result;
	}

	public Star getQishu(int groupId) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		Star star = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select * from T_Stars where state = 0 and groupId = ?" ;

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, groupId);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				star = new Star();
				star.setQishu(rs.getString("qishu"));
				star.setGroupId(rs.getInt("groupID"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return star;
	}

	public List<Star> getNewStars(String qishu) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		List<Star> stars = new ArrayList<Star>();
		Star star = null;
		String sql = null;
		try {
			if(qishu == null) {
				sql = "SELECT s.*, u.*,g.groupName, h.ImgSrc FROM " 
					+ "T_Stars as s, T_User as u,T_Group as g,T_HeadImg as h" 
					+ " where s.starId = u.id and s.groupID = 2 and u.groupID = g.id and u.headImg = h.id and u.state = 0" + 
					" order by s.id desc limit 0, 1";
			
				pstmt = dbt.getPstmt(sql);
				rs = pstmt.executeQuery();
			}else {
				sql = "SELECT s.*, u.*,g.groupName, h.ImgSrc FROM " 
					+ "T_Stars as s, T_User as u,T_Group as g,T_HeadImg as h" 
					+ " where s.qishu = ? and s.starId = u.id and s.groupID = 2 and u.groupID = g.id and u.headImg = h.id and u.state = 0" + 
					" order by s.id desc limit 0, 1";	
				pstmt = dbt.getPstmt(sql);
				pstmt.setString(1, qishu);
				rs = pstmt.executeQuery();
			}

			if(rs.next()) {
				star = new Star();
				star.setId(rs.getInt("s.id"));
				
				star.setUser(new User());
				star.getUser().setId(rs.getInt("u.id"));
				star.getUser().setName(rs.getString("u.name"));
				star.getUser().setJoinTime(rs.getDate("u.joinTime"));
				star.getUser().setSex(rs.getString("u.sex"));
				//star.getUser().setGroup(new Group(rs.getInt("groupID"),rs.getString("groupName")));
			star.getUser().setGroup(new Group(rs.getInt("u.groupID"),Encoding.isoToGBK(rs.getString("g.groupName"))));
				star.getUser().setPost(rs.getInt("u.post"));
				star.getUser().setPhone(rs.getString("u.phone"));
				star.getUser().setMajor(Encoding.isoToGBK(rs.getString("u.major")));
			//star.getUser().setMajor(rs.getString("major"));
				star.getUser().setHeadImg(new HeadImg());
				star.getUser().getHeadImg().setImgSrc(rs.getString("h.ImgSrc"));
				star.getUser().setQq(rs.getString("u.qq"));
				star.getUser().setLogintime(rs.getInt("u.loginTime"));
				star.getUser().setJiFen(rs.getInt("u.jifen"));
				
				star.setQishu(rs.getString("s.qishu"));
				star.setEvaluateDate(rs.getTimestamp("s.evaluateDate"));
				star.setDescription(rs.getString("description"));
				star.setBookName(rs.getString("s.bookName"));
				star.setState(rs.getInt("s.state"));
				
				stars.add(star);
			}
			
			if(qishu == null) {
				sql = "SELECT s.*, u.*,g.groupName, h.ImgSrc FROM " 
					+ "T_Stars as s, T_User as u,T_Group as g,T_HeadImg as h" 
					+ " where s.starId = u.id and s.groupID = 3 and u.groupID = g.id and u.headImg = h.id and u.state = 0" + 
					" order by s.id desc limit 0, 1";
			
				pstmt = dbt.getPstmt(sql);
				rs = pstmt.executeQuery();
			}else {
				sql = "SELECT s.*, u.*,g.groupName, h.ImgSrc FROM " 
					+ "T_Stars as s, T_User as u,T_Group as g,T_HeadImg as h" 
					+ " where s.qishu = ? and s.starId = u.id and s.groupID = 3 and u.groupID = g.id and u.headImg = h.id and u.state = 0" + 
					" order by s.id desc limit 0, 1";	
				pstmt = dbt.getPstmt(sql);
				pstmt.setString(1, qishu);
				rs = pstmt.executeQuery();
			}
			if(rs.next()) {
				star = new Star();
				star.setId(rs.getInt("s.id"));
				
				star.setUser(new User());
				star.getUser().setId(rs.getInt("u.id"));
				star.getUser().setName(rs.getString("u.name"));
				star.getUser().setJoinTime(rs.getDate("u.joinTime"));
				star.getUser().setSex(rs.getString("u.sex"));
				//star.getUser().setGroup(new Group(rs.getInt("groupID"),rs.getString("groupName")));
			star.getUser().setGroup(new Group(rs.getInt("u.groupID"),Encoding.isoToGBK(rs.getString("g.groupName"))));
				star.getUser().setPost(rs.getInt("u.post"));
				star.getUser().setPhone(rs.getString("u.phone"));
				star.getUser().setMajor(Encoding.isoToGBK(rs.getString("u.major")));
			//star.getUser().setMajor(rs.getString("major"));
				star.getUser().setHeadImg(new HeadImg());
				star.getUser().getHeadImg().setImgSrc(rs.getString("h.ImgSrc"));
				star.getUser().setQq(rs.getString("u.qq"));
				star.getUser().setLogintime(rs.getInt("u.loginTime"));
				star.getUser().setJiFen(rs.getInt("u.jifen"));
				
				star.setQishu(rs.getString("s.qishu"));
				star.setEvaluateDate(rs.getTimestamp("s.evaluateDate"));
				star.setDescription(rs.getString("description"));
				star.setBookName(rs.getString("s.bookName"));
				star.setState(rs.getInt("s.state"));
				
				stars.add(star);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return stars;
	}

	public Map<String, StarHistory> getStarHistory() {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		StarHistory starHistory = null;
		Map<String, StarHistory> starsMap = new HashMap<String, StarHistory>();
		Star star = null;
		String sql = "select qishu from ztinfomgr.T_Stars group by qishu;";

		try {
			pstmt = dbt.getPstmt(sql);
			rs = pstmt.executeQuery();

			while(rs.next()) {
				starHistory = new StarHistory();
				starsMap.put(rs.getString("qishu"), starHistory);
			}
			
			sql = "select s.*, u.id,u.name,g.groupName from ztinfomgr.T_Stars as s, ztinfomgr.T_User as u,ztinfomgr.T_Group as g " + 
					"where s.starId = u.id and u.groupID = g.id";
			pstmt = dbt.getPstmt(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				starHistory = starsMap.get(rs.getString("s.qishu"));
				starHistory.setQishu(rs.getString("s.qishu"));
				
				star = new Star();
				star.setUser(new User());
				star.getUser().setId(rs.getInt("u.id"));
				star.getUser().setName(rs.getString("u.name"));
				star.getUser().setGroup(new Group());
				star.getUser().getGroup().setGroupName(rs.getString("g.groupName"));
				
				star.setEvaluateDate(rs.getTimestamp("s.evaluateDate"));
				star.setDescription(rs.getString("s.description"));
				star.setBookName(rs.getString("s.bookName"));
				star.setGroupId(rs.getInt("s.groupID"));
				
				if(star.getGroupId() == 2) {
					starHistory.setStar1(star);
				}else if(star.getGroupId() == 3) {
					starHistory.setStar2(star);
				}
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return starsMap;
	}

	public int deleteStar(String qishu) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "delete from T_Stars where qishu = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, qishu);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int modifyStar(Star star) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Stars set starId = ?, evaluateDate = ? , description = ?, bookName = ?, state = 1" + 
						" where qishu = ? and groupID = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, star.getUser().getId());
			pstmt.setString(2, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setString(3, star.getDescription());
			pstmt.setString(4, star.getBookName());
			pstmt.setString(5, star.getQishu());
			pstmt.setInt(6, star.getGroupId());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}
	
}
